{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.022330Z",
     "start_time": "2017-06-04T02:08:48.797143Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format='retina'\n",
    "import matplotlib as mpl\n",
    "from matplotlib import pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os, sys\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "sns.set_context(\"poster\", font_scale=1.3)\n",
    "#http://www.lfd.uci.edu/~gohlke/pythonlibs/#xgboost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"figures/fao.jpg\" alt=\"FAO\" width=\"290\" align=\"right\">\n",
    "We will be using the [Food and Agriculture Organization](http://www.fao.org) (FAO) of the United Nation's AQUASTAT dataset. \n",
    "\n",
    "From FAO: \n",
    "\n",
    "粮农组织的三个主要目标是:\n",
    "1. 消除饥饿、粮食不安全和营养不良\n",
    "2. 消除贫困促进经济社会进步\n",
    "3. 自然资源的可持续管理和利用，包括土地、水、空气、气候和遗传资源，以造福今世后代。\n",
    "\n",
    "为支持这些目标，《宪法》第1条要求粮农组织“收集、分析、解释和传播与营养、粮食和农业有关的信息”。因此，水温自动调节器开始，其目的是通过收集有助于联合国粮农组织的目标，与水资源相关的信息传播分析，用水和农业用水管理，对国家重点在非洲，亚洲，美国，拉丁美洲，加勒比海。\n",
    "\n",
    "联合国粮农组织提供数据，元数据，报告国家概况，河流域概况，分析区域，图，表空间，数据，指导方针，和其他的在线工具:\n",
    "* 水资源：内部、跨界、总\n",
    "* 水的用途：按部门，按来源，废水\n",
    "* 灌溉：地点、面积、类型、技术、作物\n",
    "* 水坝：位置，高度，容量，表面积\n",
    "* 与水有关的机构、政策和立法"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "http://www.fao.org/nr/water/aquastat/data/query/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question: *水的供应和用水是否与人均国内生产总值有关?* "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Our plan\n",
    "\n",
    "<img src=\"figures/branches.jpg\" alt=\"Crisp-DM\" width=\"390\" align=\"right\">\n",
    "Exploratory data analysis consists of the following major tasks, which we present linearly here because each task doesn't make much sense to do without the ones prior to it. However, in reality, you are going to constantly jump around from step to step.  You may want to do all the steps for a subset of the variables first. Or often, an observation will bring up a question you want to investigate and you'll branch off and explore to answer that question before returning down the main path of exhaustive EDA.\n",
    "\n",
    "1. **Form hypotheses/develop investigation themes to explore** \n",
    "3. **Wrangle data** \n",
    "3. Assess quality of data \n",
    "4. Profile data \n",
    "5. Explore each individual variable in the dataset \n",
    "6. Assess the relationship between each variable and the target \n",
    "7. Assess interactions between variables \n",
    "8. Explore data across many dimensions \n",
    "\n",
    "Throughout the entire analysis you want to:\n",
    "* Capture a list of hypotheses and questions that come up for further exploration.\n",
    "* Record things to watch out for/ be aware of in future analyses. \n",
    "* Show intermediate results to colleagues to get a fresh perspective, feedback, domain knowledge. Don't do EDA in a bubble! Get feedback throughout especially from people removed from the problem and/or with relevant domain knowledge. \n",
    "* Position visuals and results together. EDA relies on your natural pattern recognition abilities so maximize what you'll find by putting visualizations and results in close proximity. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Things to consider doing \n",
    "\n",
    "**Make your data [tidy](https://tomaugspurger.github.io/modern-5-tidy.html)**\n",
    "1. Each variable forms a column\n",
    "2. Each observation forms a row\n",
    "3. Each type of observational unit forms a table\n",
    "\n",
    "**Transform data**  \n",
    "Sometimes you will need to transform your data to be able to extract information from it. This step will usually occur after some of the other steps of EDA unless domain knowledge can inform these choices beforehand. Transforms include:  \n",
    "\n",
    "* Log: when data is highly skewed (versus normally distributed like a bell curve), sometimes it has a log-normal distribution and taking the log of each data point will normalize it. \n",
    "* Binning of continuous variables: Binning continuous variables and then analyzing the groups of observations created can allow for easier pattern identification. Especially with non-linear relationships. \n",
    "* Simplifying of categories: you really don't want more than 8-10 categories within a single data field. Try to aggregate to higher-level categories when it makes sense. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.401952Z",
     "start_time": "2017-06-04T02:08:50.024056Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('aquastat.csv.gzip', compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.420770Z",
     "start_time": "2017-06-04T02:08:50.403986Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>variable</th>\n",
       "      <th>variable_full</th>\n",
       "      <th>time_period</th>\n",
       "      <th>year_measured</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>World | Asia</td>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "      <td>1958-1962</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>65286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>World | Asia</td>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "      <td>1963-1967</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>65286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>World | Asia</td>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "      <td>1968-1972</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>65286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>World | Asia</td>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "      <td>1973-1977</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>65286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>World | Asia</td>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "      <td>1978-1982</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>65286.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country        region    variable                        variable_full  \\\n",
       "0  Afghanistan  World | Asia  total_area  Total area of the country (1000 ha)   \n",
       "1  Afghanistan  World | Asia  total_area  Total area of the country (1000 ha)   \n",
       "2  Afghanistan  World | Asia  total_area  Total area of the country (1000 ha)   \n",
       "3  Afghanistan  World | Asia  total_area  Total area of the country (1000 ha)   \n",
       "4  Afghanistan  World | Asia  total_area  Total area of the country (1000 ha)   \n",
       "\n",
       "  time_period  year_measured    value  \n",
       "0   1958-1962         1962.0  65286.0  \n",
       "1   1963-1967         1967.0  65286.0  \n",
       "2   1968-1972         1972.0  65286.0  \n",
       "3   1973-1977         1977.0  65286.0  \n",
       "4   1978-1982         1982.0  65286.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(143280, 7)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.478416Z",
     "start_time": "2017-06-04T02:08:50.422184Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 143280 entries, 0 to 143279\n",
      "Data columns (total 7 columns):\n",
      "country          143280 non-null object\n",
      "region           143280 non-null object\n",
      "variable         143280 non-null object\n",
      "variable_full    143280 non-null object\n",
      "time_period      143280 non-null object\n",
      "year_measured    96411 non-null float64\n",
      "value            96411 non-null float64\n",
      "dtypes: float64(2), object(5)\n",
      "memory usage: 7.7+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Research the variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variable</th>\n",
       "      <th>variable_full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143250</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143251</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143252</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143253</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143254</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143255</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143256</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143257</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143258</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143259</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143260</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143261</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143262</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143263</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143264</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143265</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143266</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143267</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143268</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143269</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143270</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143271</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143272</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143273</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143274</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143275</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143276</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143277</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143278</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143279</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>143280 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         variable  \\\n",
       "0                      total_area   \n",
       "1                      total_area   \n",
       "2                      total_area   \n",
       "3                      total_area   \n",
       "4                      total_area   \n",
       "5                      total_area   \n",
       "6                      total_area   \n",
       "7                      total_area   \n",
       "8                      total_area   \n",
       "9                      total_area   \n",
       "10                     total_area   \n",
       "11                     total_area   \n",
       "12                     total_area   \n",
       "13                     total_area   \n",
       "14                     total_area   \n",
       "15                     total_area   \n",
       "16                     total_area   \n",
       "17                     total_area   \n",
       "18                     total_area   \n",
       "19                     total_area   \n",
       "20                     total_area   \n",
       "21                     total_area   \n",
       "22                     total_area   \n",
       "23                     total_area   \n",
       "24                     total_area   \n",
       "25                     total_area   \n",
       "26                     total_area   \n",
       "27                     total_area   \n",
       "28                     total_area   \n",
       "29                     total_area   \n",
       "...                           ...   \n",
       "143250  urban_pop_access_drinking   \n",
       "143251  urban_pop_access_drinking   \n",
       "143252  urban_pop_access_drinking   \n",
       "143253  urban_pop_access_drinking   \n",
       "143254  urban_pop_access_drinking   \n",
       "143255  urban_pop_access_drinking   \n",
       "143256  urban_pop_access_drinking   \n",
       "143257  urban_pop_access_drinking   \n",
       "143258  urban_pop_access_drinking   \n",
       "143259  urban_pop_access_drinking   \n",
       "143260  urban_pop_access_drinking   \n",
       "143261  urban_pop_access_drinking   \n",
       "143262  urban_pop_access_drinking   \n",
       "143263  urban_pop_access_drinking   \n",
       "143264  urban_pop_access_drinking   \n",
       "143265  urban_pop_access_drinking   \n",
       "143266  urban_pop_access_drinking   \n",
       "143267  urban_pop_access_drinking   \n",
       "143268  urban_pop_access_drinking   \n",
       "143269  urban_pop_access_drinking   \n",
       "143270  urban_pop_access_drinking   \n",
       "143271  urban_pop_access_drinking   \n",
       "143272  urban_pop_access_drinking   \n",
       "143273  urban_pop_access_drinking   \n",
       "143274  urban_pop_access_drinking   \n",
       "143275  urban_pop_access_drinking   \n",
       "143276  urban_pop_access_drinking   \n",
       "143277  urban_pop_access_drinking   \n",
       "143278  urban_pop_access_drinking   \n",
       "143279  urban_pop_access_drinking   \n",
       "\n",
       "                                            variable_full  \n",
       "0                     Total area of the country (1000 ha)  \n",
       "1                     Total area of the country (1000 ha)  \n",
       "2                     Total area of the country (1000 ha)  \n",
       "3                     Total area of the country (1000 ha)  \n",
       "4                     Total area of the country (1000 ha)  \n",
       "5                     Total area of the country (1000 ha)  \n",
       "6                     Total area of the country (1000 ha)  \n",
       "7                     Total area of the country (1000 ha)  \n",
       "8                     Total area of the country (1000 ha)  \n",
       "9                     Total area of the country (1000 ha)  \n",
       "10                    Total area of the country (1000 ha)  \n",
       "11                    Total area of the country (1000 ha)  \n",
       "12                    Total area of the country (1000 ha)  \n",
       "13                    Total area of the country (1000 ha)  \n",
       "14                    Total area of the country (1000 ha)  \n",
       "15                    Total area of the country (1000 ha)  \n",
       "16                    Total area of the country (1000 ha)  \n",
       "17                    Total area of the country (1000 ha)  \n",
       "18                    Total area of the country (1000 ha)  \n",
       "19                    Total area of the country (1000 ha)  \n",
       "20                    Total area of the country (1000 ha)  \n",
       "21                    Total area of the country (1000 ha)  \n",
       "22                    Total area of the country (1000 ha)  \n",
       "23                    Total area of the country (1000 ha)  \n",
       "24                    Total area of the country (1000 ha)  \n",
       "25                    Total area of the country (1000 ha)  \n",
       "26                    Total area of the country (1000 ha)  \n",
       "27                    Total area of the country (1000 ha)  \n",
       "28                    Total area of the country (1000 ha)  \n",
       "29                    Total area of the country (1000 ha)  \n",
       "...                                                   ...  \n",
       "143250  Urban population with access to safe drinking-...  \n",
       "143251  Urban population with access to safe drinking-...  \n",
       "143252  Urban population with access to safe drinking-...  \n",
       "143253  Urban population with access to safe drinking-...  \n",
       "143254  Urban population with access to safe drinking-...  \n",
       "143255  Urban population with access to safe drinking-...  \n",
       "143256  Urban population with access to safe drinking-...  \n",
       "143257  Urban population with access to safe drinking-...  \n",
       "143258  Urban population with access to safe drinking-...  \n",
       "143259  Urban population with access to safe drinking-...  \n",
       "143260  Urban population with access to safe drinking-...  \n",
       "143261  Urban population with access to safe drinking-...  \n",
       "143262  Urban population with access to safe drinking-...  \n",
       "143263  Urban population with access to safe drinking-...  \n",
       "143264  Urban population with access to safe drinking-...  \n",
       "143265  Urban population with access to safe drinking-...  \n",
       "143266  Urban population with access to safe drinking-...  \n",
       "143267  Urban population with access to safe drinking-...  \n",
       "143268  Urban population with access to safe drinking-...  \n",
       "143269  Urban population with access to safe drinking-...  \n",
       "143270  Urban population with access to safe drinking-...  \n",
       "143271  Urban population with access to safe drinking-...  \n",
       "143272  Urban population with access to safe drinking-...  \n",
       "143273  Urban population with access to safe drinking-...  \n",
       "143274  Urban population with access to safe drinking-...  \n",
       "143275  Urban population with access to safe drinking-...  \n",
       "143276  Urban population with access to safe drinking-...  \n",
       "143277  Urban population with access to safe drinking-...  \n",
       "143278  Urban population with access to safe drinking-...  \n",
       "143279  Urban population with access to safe drinking-...  \n",
       "\n",
       "[143280 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data[['variable','variable_full']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.521736Z",
     "start_time": "2017-06-04T02:08:50.481551Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variable</th>\n",
       "      <th>variable_full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_area</td>\n",
       "      <td>Total area of the country (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>arable_land</td>\n",
       "      <td>Arable land area (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1152</th>\n",
       "      <td>permanent_crop_area</td>\n",
       "      <td>Permanent crops area (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1728</th>\n",
       "      <td>cultivated_area</td>\n",
       "      <td>Cultivated area (arable land + permanent crops...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2304</th>\n",
       "      <td>percent_cultivated</td>\n",
       "      <td>% of total country area cultivated (%)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2880</th>\n",
       "      <td>total_pop</td>\n",
       "      <td>Total population (1000 inhab)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3456</th>\n",
       "      <td>rural_pop</td>\n",
       "      <td>Rural population (1000 inhab)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4032</th>\n",
       "      <td>urban_pop</td>\n",
       "      <td>Urban population (1000 inhab)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4608</th>\n",
       "      <td>gdp</td>\n",
       "      <td>Gross Domestic Product (GDP) (current US$)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5184</th>\n",
       "      <td>gdp_per_capita</td>\n",
       "      <td>GDP per capita (current US$/inhab)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5760</th>\n",
       "      <td>agg_to_gdp</td>\n",
       "      <td>Agriculture, value added to GDP (%)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6336</th>\n",
       "      <td>human_dev_index</td>\n",
       "      <td>Human Development Index (HDI) [highest = 1] (-)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6912</th>\n",
       "      <td>gender_inequal_index</td>\n",
       "      <td>Gender Inequality Index (GII) [equality = 0; i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7488</th>\n",
       "      <td>percent_undernourished</td>\n",
       "      <td>Prevalence of undernourishment (3-year average...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8064</th>\n",
       "      <td>number_undernourished</td>\n",
       "      <td>Number of people undernourished (3-year averag...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8640</th>\n",
       "      <td>avg_annual_rain_depth</td>\n",
       "      <td>Long-term average annual precipitation in dept...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9216</th>\n",
       "      <td>avg_annual_rain_vol</td>\n",
       "      <td>Long-term average annual precipitation in volu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9792</th>\n",
       "      <td>national_rainfall_index</td>\n",
       "      <td>National Rainfall Index (NRI) (mm/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10368</th>\n",
       "      <td>surface_water_produced</td>\n",
       "      <td>Surface water produced internally (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10944</th>\n",
       "      <td>groundwater_produced</td>\n",
       "      <td>Groundwater produced internally (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11520</th>\n",
       "      <td>surface_groundwater_overlap</td>\n",
       "      <td>Overlap between surface water and groundwater ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12096</th>\n",
       "      <td>irwr</td>\n",
       "      <td>Total internal renewable water resources (IRWR...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12672</th>\n",
       "      <td>irwr_per_capita</td>\n",
       "      <td>Total internal renewable water resources per c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13248</th>\n",
       "      <td>surface_entering</td>\n",
       "      <td>Surface water: entering the country (total) (1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13824</th>\n",
       "      <td>surface_inflow_submit_no_treaty</td>\n",
       "      <td>Surface water: inflow not submitted to treatie...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14400</th>\n",
       "      <td>surface_inflow_submit_treaty</td>\n",
       "      <td>Surface water: inflow submitted to treaties (1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14976</th>\n",
       "      <td>surface_inflow_secure_treaty</td>\n",
       "      <td>Surface water: inflow secured through treaties...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15552</th>\n",
       "      <td>total_flow_border_rivers</td>\n",
       "      <td>Surface water: total flow of border rivers (10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16128</th>\n",
       "      <td>accounted_flow_border_rivers</td>\n",
       "      <td>Surface water: accounted flow of border rivers...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16704</th>\n",
       "      <td>accounted_flow</td>\n",
       "      <td>Surface water: accounted inflow (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17280</th>\n",
       "      <td>surface_to_other_countries</td>\n",
       "      <td>Surface water: leaving the country to other co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17856</th>\n",
       "      <td>surface_outflow_submit_no_treaty</td>\n",
       "      <td>Surface water: outflow to other countries not ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18432</th>\n",
       "      <td>surface_outflow_submit_treaty</td>\n",
       "      <td>Surface water: outflow to other countries subm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19008</th>\n",
       "      <td>surface_outflow_secure_treaty</td>\n",
       "      <td>Surface water: outflow to other countries secu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19584</th>\n",
       "      <td>surface_total_external_renewable</td>\n",
       "      <td>Surface water: total external renewable (10^9 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20160</th>\n",
       "      <td>groundwater_entering</td>\n",
       "      <td>Groundwater: entering the country (total) (10^...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20736</th>\n",
       "      <td>groundwater_accounted_inflow</td>\n",
       "      <td>Groundwater: accounted inflow (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21312</th>\n",
       "      <td>groundwater_to_other_countries</td>\n",
       "      <td>Groundwater: leaving the country to other coun...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21888</th>\n",
       "      <td>groundwater_accounted_outflow</td>\n",
       "      <td>Groundwater: accounted outflow to other countr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22464</th>\n",
       "      <td>water_total_external_renewable</td>\n",
       "      <td>Water resources: total external renewable (10^...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23040</th>\n",
       "      <td>total_renewable_surface</td>\n",
       "      <td>Total renewable surface water (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23616</th>\n",
       "      <td>total_renewable_groundwater</td>\n",
       "      <td>Total renewable groundwater (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24192</th>\n",
       "      <td>overlap_surface_groundwater</td>\n",
       "      <td>Overlap: between surface water and groundwater...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24768</th>\n",
       "      <td>total_renewable</td>\n",
       "      <td>Total renewable water resources (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25344</th>\n",
       "      <td>dependency_ratio</td>\n",
       "      <td>Dependency ratio (%)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25920</th>\n",
       "      <td>total_renewable_per_capita</td>\n",
       "      <td>Total renewable water resources per capita (m3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26496</th>\n",
       "      <td>exploitable_regular_renewable_surface</td>\n",
       "      <td>Exploitable: regular renewable surface water (...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27072</th>\n",
       "      <td>exploitable_irregular_renewable_surface</td>\n",
       "      <td>Exploitable: irregular renewable surface water...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27648</th>\n",
       "      <td>exploitable_total_renewable_surface</td>\n",
       "      <td>Exploitable: total renewable surface water (10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28224</th>\n",
       "      <td>exploitable_regular_renewable_groundwater</td>\n",
       "      <td>Exploitable: regular renewable groundwater (10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28800</th>\n",
       "      <td>exploitable_total</td>\n",
       "      <td>Total exploitable water resources (10^9 m3/year)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29376</th>\n",
       "      <td>interannual_variability</td>\n",
       "      <td>Interannual variability (WRI) (-)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29952</th>\n",
       "      <td>seasonal_variability</td>\n",
       "      <td>Seasonal variability (WRI) (-)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30528</th>\n",
       "      <td>total_dam_capacity</td>\n",
       "      <td>Total dam capacity (km3)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31104</th>\n",
       "      <td>dam_capacity_per_capita</td>\n",
       "      <td>Dam capacity per capita (m3/inhab)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31680</th>\n",
       "      <td>irrigation_potential</td>\n",
       "      <td>Irrigation potential (1000 ha)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32256</th>\n",
       "      <td>flood_occurence</td>\n",
       "      <td>Flood occurrence (WRI) (-)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32832</th>\n",
       "      <td>total_pop_access_drinking</td>\n",
       "      <td>Total population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33408</th>\n",
       "      <td>rural_pop_access_drinking</td>\n",
       "      <td>Rural population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33984</th>\n",
       "      <td>urban_pop_access_drinking</td>\n",
       "      <td>Urban population with access to safe drinking-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        variable  \\\n",
       "0                                     total_area   \n",
       "576                                  arable_land   \n",
       "1152                         permanent_crop_area   \n",
       "1728                             cultivated_area   \n",
       "2304                          percent_cultivated   \n",
       "2880                                   total_pop   \n",
       "3456                                   rural_pop   \n",
       "4032                                   urban_pop   \n",
       "4608                                         gdp   \n",
       "5184                              gdp_per_capita   \n",
       "5760                                  agg_to_gdp   \n",
       "6336                             human_dev_index   \n",
       "6912                        gender_inequal_index   \n",
       "7488                      percent_undernourished   \n",
       "8064                       number_undernourished   \n",
       "8640                       avg_annual_rain_depth   \n",
       "9216                         avg_annual_rain_vol   \n",
       "9792                     national_rainfall_index   \n",
       "10368                     surface_water_produced   \n",
       "10944                       groundwater_produced   \n",
       "11520                surface_groundwater_overlap   \n",
       "12096                                       irwr   \n",
       "12672                            irwr_per_capita   \n",
       "13248                           surface_entering   \n",
       "13824            surface_inflow_submit_no_treaty   \n",
       "14400               surface_inflow_submit_treaty   \n",
       "14976               surface_inflow_secure_treaty   \n",
       "15552                   total_flow_border_rivers   \n",
       "16128               accounted_flow_border_rivers   \n",
       "16704                             accounted_flow   \n",
       "17280                 surface_to_other_countries   \n",
       "17856           surface_outflow_submit_no_treaty   \n",
       "18432              surface_outflow_submit_treaty   \n",
       "19008              surface_outflow_secure_treaty   \n",
       "19584           surface_total_external_renewable   \n",
       "20160                       groundwater_entering   \n",
       "20736               groundwater_accounted_inflow   \n",
       "21312             groundwater_to_other_countries   \n",
       "21888              groundwater_accounted_outflow   \n",
       "22464             water_total_external_renewable   \n",
       "23040                    total_renewable_surface   \n",
       "23616                total_renewable_groundwater   \n",
       "24192                overlap_surface_groundwater   \n",
       "24768                            total_renewable   \n",
       "25344                           dependency_ratio   \n",
       "25920                 total_renewable_per_capita   \n",
       "26496      exploitable_regular_renewable_surface   \n",
       "27072    exploitable_irregular_renewable_surface   \n",
       "27648        exploitable_total_renewable_surface   \n",
       "28224  exploitable_regular_renewable_groundwater   \n",
       "28800                          exploitable_total   \n",
       "29376                    interannual_variability   \n",
       "29952                       seasonal_variability   \n",
       "30528                         total_dam_capacity   \n",
       "31104                    dam_capacity_per_capita   \n",
       "31680                       irrigation_potential   \n",
       "32256                            flood_occurence   \n",
       "32832                  total_pop_access_drinking   \n",
       "33408                  rural_pop_access_drinking   \n",
       "33984                  urban_pop_access_drinking   \n",
       "\n",
       "                                           variable_full  \n",
       "0                    Total area of the country (1000 ha)  \n",
       "576                           Arable land area (1000 ha)  \n",
       "1152                      Permanent crops area (1000 ha)  \n",
       "1728   Cultivated area (arable land + permanent crops...  \n",
       "2304              % of total country area cultivated (%)  \n",
       "2880                       Total population (1000 inhab)  \n",
       "3456                       Rural population (1000 inhab)  \n",
       "4032                       Urban population (1000 inhab)  \n",
       "4608          Gross Domestic Product (GDP) (current US$)  \n",
       "5184                  GDP per capita (current US$/inhab)  \n",
       "5760                 Agriculture, value added to GDP (%)  \n",
       "6336     Human Development Index (HDI) [highest = 1] (-)  \n",
       "6912   Gender Inequality Index (GII) [equality = 0; i...  \n",
       "7488   Prevalence of undernourishment (3-year average...  \n",
       "8064   Number of people undernourished (3-year averag...  \n",
       "8640   Long-term average annual precipitation in dept...  \n",
       "9216   Long-term average annual precipitation in volu...  \n",
       "9792             National Rainfall Index (NRI) (mm/year)  \n",
       "10368   Surface water produced internally (10^9 m3/year)  \n",
       "10944     Groundwater produced internally (10^9 m3/year)  \n",
       "11520  Overlap between surface water and groundwater ...  \n",
       "12096  Total internal renewable water resources (IRWR...  \n",
       "12672  Total internal renewable water resources per c...  \n",
       "13248  Surface water: entering the country (total) (1...  \n",
       "13824  Surface water: inflow not submitted to treatie...  \n",
       "14400  Surface water: inflow submitted to treaties (1...  \n",
       "14976  Surface water: inflow secured through treaties...  \n",
       "15552  Surface water: total flow of border rivers (10...  \n",
       "16128  Surface water: accounted flow of border rivers...  \n",
       "16704     Surface water: accounted inflow (10^9 m3/year)  \n",
       "17280  Surface water: leaving the country to other co...  \n",
       "17856  Surface water: outflow to other countries not ...  \n",
       "18432  Surface water: outflow to other countries subm...  \n",
       "19008  Surface water: outflow to other countries secu...  \n",
       "19584  Surface water: total external renewable (10^9 ...  \n",
       "20160  Groundwater: entering the country (total) (10^...  \n",
       "20736       Groundwater: accounted inflow (10^9 m3/year)  \n",
       "21312  Groundwater: leaving the country to other coun...  \n",
       "21888  Groundwater: accounted outflow to other countr...  \n",
       "22464  Water resources: total external renewable (10^...  \n",
       "23040       Total renewable surface water (10^9 m3/year)  \n",
       "23616         Total renewable groundwater (10^9 m3/year)  \n",
       "24192  Overlap: between surface water and groundwater...  \n",
       "24768     Total renewable water resources (10^9 m3/year)  \n",
       "25344                               Dependency ratio (%)  \n",
       "25920  Total renewable water resources per capita (m3...  \n",
       "26496  Exploitable: regular renewable surface water (...  \n",
       "27072  Exploitable: irregular renewable surface water...  \n",
       "27648  Exploitable: total renewable surface water (10...  \n",
       "28224  Exploitable: regular renewable groundwater (10...  \n",
       "28800   Total exploitable water resources (10^9 m3/year)  \n",
       "29376                  Interannual variability (WRI) (-)  \n",
       "29952                     Seasonal variability (WRI) (-)  \n",
       "30528                           Total dam capacity (km3)  \n",
       "31104                 Dam capacity per capita (m3/inhab)  \n",
       "31680                     Irrigation potential (1000 ha)  \n",
       "32256                         Flood occurrence (WRI) (-)  \n",
       "32832  Total population with access to safe drinking-...  \n",
       "33408  Rural population with access to safe drinking-...  \n",
       "33984  Urban population with access to safe drinking-...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['variable','variable_full']].drop_duplicates()\n",
    "#total_area 国土面积（1000公顷）\n",
    "#arable_land 可耕作面积\n",
    "#permanent_crop_area 多年生作物面积\n",
    "#cultivated_area 耕地面积\n",
    "#percent_cultivated 耕地面积占比\n",
    "#total_pop 总人口\n",
    "#rural_pop 农村人口\n",
    "#urban_pop 城市人口\n",
    "#gdp 国内生产总值\n",
    "#gdp_per_capita 人均国内生产总值\n",
    "#agg_to_gdp 农业，增加国内生产总值\n",
    "#human_dev_index 人类发展指数\n",
    "#gender_inequal_index 性别不平等指数\n",
    "#percent_undernourished 营养不良患病率\n",
    "#avg_annual_rain_depth 长期平均年降水量\n",
    "#national_rainfall_index 全国降雨指数 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Describe the panel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "199 unique countries involved"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.536064Z",
     "start_time": "2017-06-04T02:08:50.523339Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "199"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.country.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.549182Z",
     "start_time": "2017-06-04T02:08:50.537760Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "countries = data.country.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For 12 time periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.560889Z",
     "start_time": "2017-06-04T02:08:50.551184Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.time_period.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each 5 years in length since 1958"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.571089Z",
     "start_time": "2017-06-04T02:08:50.562475Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1958-1962' '1963-1967' '1968-1972' '1973-1977' '1978-1982' '1983-1987'\n",
      " '1988-1992' '1993-1997' '1998-2002' '2003-2007' '2008-2012' '2013-2017']\n"
     ]
    }
   ],
   "source": [
    "time_periods = data.time_period.unique()\n",
    "print(time_periods)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.575054Z",
     "start_time": "2017-06-04T02:08:50.572585Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "mid_periods = range(1960,2017,5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dataset is unbalanced because there is not data for every country at every time period (more on missing data in the next notebook)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.596298Z",
     "start_time": "2017-06-04T02:08:50.576506Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "220"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data.variable=='total_area'].value.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ways to look at this data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can look at this data set in a number of ways: \n",
    "* 横截面：一个时期内所有国家\n",
    "* 时间序列：一个国家随着时间的推移\n",
    "* 面板数据：所有国家随着时间的推移（作为数据给出）\n",
    "* 地理空间：所有地理上相互联系的国家"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Slicing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For a given time slice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.607555Z",
     "start_time": "2017-06-04T02:08:50.597801Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def time_slice(df, time_period):\n",
    "\n",
    "    # Only take data for time period of interest\n",
    "    df = df[df.time_period==time_period] \n",
    "\n",
    "    # Pivot table \n",
    "    df = df.pivot(index='country', columns='variable', values='value')\n",
    "    \n",
    "    df.columns.name = time_period\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.659109Z",
     "start_time": "2017-06-04T02:08:50.609475Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1958-1962</th>\n",
       "      <th>accounted_flow</th>\n",
       "      <th>accounted_flow_border_rivers</th>\n",
       "      <th>agg_to_gdp</th>\n",
       "      <th>arable_land</th>\n",
       "      <th>avg_annual_rain_depth</th>\n",
       "      <th>avg_annual_rain_vol</th>\n",
       "      <th>cultivated_area</th>\n",
       "      <th>dam_capacity_per_capita</th>\n",
       "      <th>dependency_ratio</th>\n",
       "      <th>exploitable_irregular_renewable_surface</th>\n",
       "      <th>...</th>\n",
       "      <th>total_flow_border_rivers</th>\n",
       "      <th>total_pop</th>\n",
       "      <th>total_pop_access_drinking</th>\n",
       "      <th>total_renewable</th>\n",
       "      <th>total_renewable_groundwater</th>\n",
       "      <th>total_renewable_per_capita</th>\n",
       "      <th>total_renewable_surface</th>\n",
       "      <th>urban_pop</th>\n",
       "      <th>urban_pop_access_drinking</th>\n",
       "      <th>water_total_external_renewable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>19.00</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7700.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>213.5000</td>\n",
       "      <td>7760.0</td>\n",
       "      <td>128.40</td>\n",
       "      <td>28.7200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>33.4</td>\n",
       "      <td>9344.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.3300</td>\n",
       "      <td>10.650</td>\n",
       "      <td>6992.0</td>\n",
       "      <td>55.68</td>\n",
       "      <td>804.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>3.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>436.0</td>\n",
       "      <td>1485.0</td>\n",
       "      <td>42.6900</td>\n",
       "      <td>487.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.9300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1738.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.2000</td>\n",
       "      <td>6.200</td>\n",
       "      <td>17376.0</td>\n",
       "      <td>26.35</td>\n",
       "      <td>533.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>0.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6300.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>212.0000</td>\n",
       "      <td>6900.0</td>\n",
       "      <td>89.99</td>\n",
       "      <td>3.5990</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11690.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.6700</td>\n",
       "      <td>1.517</td>\n",
       "      <td>998.3</td>\n",
       "      <td>10.15</td>\n",
       "      <td>3934.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.4724</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.3156</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20520.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.76</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <td>0.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>1010.0</td>\n",
       "      <td>1259.0000</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>25.96</td>\n",
       "      <td>0.2695</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5466.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>148.4000</td>\n",
       "      <td>58.000</td>\n",
       "      <td>27150.0</td>\n",
       "      <td>145.40</td>\n",
       "      <td>577.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 60 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "1958-1962    accounted_flow  accounted_flow_border_rivers  agg_to_gdp  \\\n",
       "country                                                                 \n",
       "Afghanistan           19.00                           9.0         NaN   \n",
       "Albania                3.30                           0.0         NaN   \n",
       "Algeria                0.39                           0.0         NaN   \n",
       "Andorra                 NaN                           NaN         NaN   \n",
       "Angola                 0.40                           0.0         NaN   \n",
       "\n",
       "1958-1962    arable_land  avg_annual_rain_depth  avg_annual_rain_vol  \\\n",
       "country                                                                \n",
       "Afghanistan       7700.0                  327.0             213.5000   \n",
       "Albania            436.0                 1485.0              42.6900   \n",
       "Algeria           6300.0                   89.0             212.0000   \n",
       "Andorra              1.0                    NaN               0.4724   \n",
       "Angola            2700.0                 1010.0            1259.0000   \n",
       "\n",
       "1958-1962    cultivated_area  dam_capacity_per_capita  dependency_ratio  \\\n",
       "country                                                                   \n",
       "Afghanistan           7760.0                   128.40           28.7200   \n",
       "Albania                487.0                      NaN           10.9300   \n",
       "Algeria               6900.0                    89.99            3.5990   \n",
       "Andorra                  1.0                      NaN               NaN   \n",
       "Angola                3200.0                    25.96            0.2695   \n",
       "\n",
       "1958-1962    exploitable_irregular_renewable_surface  ...  \\\n",
       "country                                               ...   \n",
       "Afghanistan                                      NaN  ...   \n",
       "Albania                                          NaN  ...   \n",
       "Algeria                                          5.0  ...   \n",
       "Andorra                                          NaN  ...   \n",
       "Angola                                           NaN  ...   \n",
       "\n",
       "1958-1962    total_flow_border_rivers  total_pop  total_pop_access_drinking  \\\n",
       "country                                                                       \n",
       "Afghanistan                      33.4    9344.00                        NaN   \n",
       "Albania                           0.0    1738.00                        NaN   \n",
       "Algeria                           0.0   11690.00                        NaN   \n",
       "Andorra                           NaN      15.38                        NaN   \n",
       "Angola                            0.0    5466.00                        NaN   \n",
       "\n",
       "1958-1962    total_renewable  total_renewable_groundwater  \\\n",
       "country                                                     \n",
       "Afghanistan          65.3300                       10.650   \n",
       "Albania              30.2000                        6.200   \n",
       "Algeria              11.6700                        1.517   \n",
       "Andorra               0.3156                          NaN   \n",
       "Angola              148.4000                       58.000   \n",
       "\n",
       "1958-1962    total_renewable_per_capita  total_renewable_surface  urban_pop  \\\n",
       "country                                                                       \n",
       "Afghanistan                      6992.0                    55.68     804.90   \n",
       "Albania                         17376.0                    26.35     533.20   \n",
       "Algeria                           998.3                    10.15    3934.00   \n",
       "Andorra                         20520.0                      NaN       9.76   \n",
       "Angola                          27150.0                   145.40     577.00   \n",
       "\n",
       "1958-1962    urban_pop_access_drinking  water_total_external_renewable  \n",
       "country                                                                 \n",
       "Afghanistan                        NaN                           18.18  \n",
       "Albania                            NaN                            3.30  \n",
       "Algeria                            NaN                            0.42  \n",
       "Andorra                            NaN                             NaN  \n",
       "Angola                             NaN                            0.40  \n",
       "\n",
       "[5 rows x 60 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_slice(data, time_periods[0]).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For a given country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.671579Z",
     "start_time": "2017-06-04T02:08:50.661579Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def country_slice(df, country):\n",
    "    \n",
    "    # Only take data for country of interest\n",
    "    df = df[df.country==country] \n",
    "\n",
    "    # Pivot table \n",
    "    df = df.pivot(index='variable', columns='time_period', values='value')\n",
    "    \n",
    "    #df.index.name = country\n",
    "    return df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Thailand'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries[40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.707644Z",
     "start_time": "2017-06-04T02:08:50.673331Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>time_period</th>\n",
       "      <th>1958-1962</th>\n",
       "      <th>1963-1967</th>\n",
       "      <th>1968-1972</th>\n",
       "      <th>1973-1977</th>\n",
       "      <th>1978-1982</th>\n",
       "      <th>1983-1987</th>\n",
       "      <th>1988-1992</th>\n",
       "      <th>1993-1997</th>\n",
       "      <th>1998-2002</th>\n",
       "      <th>2003-2007</th>\n",
       "      <th>2008-2012</th>\n",
       "      <th>2013-2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>accounted_flow</th>\n",
       "      <td>214.1</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.1</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>accounted_flow_border_rivers</th>\n",
       "      <td>214.1</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.1</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.100</td>\n",
       "      <td>214.10</td>\n",
       "      <td>214.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agg_to_gdp</th>\n",
       "      <td>34.0</td>\n",
       "      <td>29.24</td>\n",
       "      <td>25.34</td>\n",
       "      <td>24.78</td>\n",
       "      <td>18.55</td>\n",
       "      <td>15.73</td>\n",
       "      <td>12.3</td>\n",
       "      <td>9.067</td>\n",
       "      <td>8.696</td>\n",
       "      <td>9.351</td>\n",
       "      <td>11.57</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arable_land</th>\n",
       "      <td>10600.0</td>\n",
       "      <td>11600.00</td>\n",
       "      <td>13150.00</td>\n",
       "      <td>15773.00</td>\n",
       "      <td>17199.00</td>\n",
       "      <td>17930.00</td>\n",
       "      <td>17238.0</td>\n",
       "      <td>16242.000</td>\n",
       "      <td>15389.000</td>\n",
       "      <td>15200.000</td>\n",
       "      <td>16560.00</td>\n",
       "      <td>16810.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg_annual_rain_depth</th>\n",
       "      <td>1622.0</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.0</td>\n",
       "      <td>1622.000</td>\n",
       "      <td>1622.000</td>\n",
       "      <td>1622.000</td>\n",
       "      <td>1622.00</td>\n",
       "      <td>1622.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "time_period                   1958-1962  1963-1967  1968-1972  1973-1977  \\\n",
       "variable                                                                   \n",
       "accounted_flow                    214.1     214.10     214.10     214.10   \n",
       "accounted_flow_border_rivers      214.1     214.10     214.10     214.10   \n",
       "agg_to_gdp                         34.0      29.24      25.34      24.78   \n",
       "arable_land                     10600.0   11600.00   13150.00   15773.00   \n",
       "avg_annual_rain_depth            1622.0    1622.00    1622.00    1622.00   \n",
       "\n",
       "time_period                   1978-1982  1983-1987  1988-1992  1993-1997  \\\n",
       "variable                                                                   \n",
       "accounted_flow                   214.10     214.10      214.1    214.100   \n",
       "accounted_flow_border_rivers     214.10     214.10      214.1    214.100   \n",
       "agg_to_gdp                        18.55      15.73       12.3      9.067   \n",
       "arable_land                    17199.00   17930.00    17238.0  16242.000   \n",
       "avg_annual_rain_depth           1622.00    1622.00     1622.0   1622.000   \n",
       "\n",
       "time_period                   1998-2002  2003-2007  2008-2012  2013-2017  \n",
       "variable                                                                  \n",
       "accounted_flow                  214.100    214.100     214.10      214.1  \n",
       "accounted_flow_border_rivers    214.100    214.100     214.10      214.1  \n",
       "agg_to_gdp                        8.696      9.351      11.57       10.5  \n",
       "arable_land                   15389.000  15200.000   16560.00    16810.0  \n",
       "avg_annual_rain_depth          1622.000   1622.000    1622.00     1622.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_slice(data, countries[40]).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### By variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.716546Z",
     "start_time": "2017-06-04T02:08:50.709457Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def variable_slice(df, variable):\n",
    "    \n",
    "    # Only data for that variable\n",
    "    df = df[df.variable==variable]\n",
    "    \n",
    "    # Get variable for each country over the time periods \n",
    "    df = df.pivot(index='country', columns='time_period', values='value')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.750392Z",
     "start_time": "2017-06-04T02:08:50.718454Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>time_period</th>\n",
       "      <th>1958-1962</th>\n",
       "      <th>1963-1967</th>\n",
       "      <th>1968-1972</th>\n",
       "      <th>1973-1977</th>\n",
       "      <th>1978-1982</th>\n",
       "      <th>1983-1987</th>\n",
       "      <th>1988-1992</th>\n",
       "      <th>1993-1997</th>\n",
       "      <th>1998-2002</th>\n",
       "      <th>2003-2007</th>\n",
       "      <th>2008-2012</th>\n",
       "      <th>2013-2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>9344.00</td>\n",
       "      <td>10369.00</td>\n",
       "      <td>11717.00</td>\n",
       "      <td>13056.00</td>\n",
       "      <td>12667.00</td>\n",
       "      <td>11338.00</td>\n",
       "      <td>13746.0</td>\n",
       "      <td>18034.00</td>\n",
       "      <td>21487.00</td>\n",
       "      <td>25878.00</td>\n",
       "      <td>29727.00</td>\n",
       "      <td>32527.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>1738.00</td>\n",
       "      <td>1999.00</td>\n",
       "      <td>2254.00</td>\n",
       "      <td>2518.00</td>\n",
       "      <td>2788.00</td>\n",
       "      <td>3121.00</td>\n",
       "      <td>3241.0</td>\n",
       "      <td>3092.00</td>\n",
       "      <td>3123.00</td>\n",
       "      <td>3011.00</td>\n",
       "      <td>2881.00</td>\n",
       "      <td>2897.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>11690.00</td>\n",
       "      <td>13354.00</td>\n",
       "      <td>15377.00</td>\n",
       "      <td>17690.00</td>\n",
       "      <td>20576.00</td>\n",
       "      <td>23918.00</td>\n",
       "      <td>27181.0</td>\n",
       "      <td>29888.00</td>\n",
       "      <td>31990.00</td>\n",
       "      <td>34262.00</td>\n",
       "      <td>37439.00</td>\n",
       "      <td>39667.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorra</th>\n",
       "      <td>15.38</td>\n",
       "      <td>20.75</td>\n",
       "      <td>26.89</td>\n",
       "      <td>32.77</td>\n",
       "      <td>39.11</td>\n",
       "      <td>48.46</td>\n",
       "      <td>58.9</td>\n",
       "      <td>64.15</td>\n",
       "      <td>71.05</td>\n",
       "      <td>84.88</td>\n",
       "      <td>79.32</td>\n",
       "      <td>70.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <td>5466.00</td>\n",
       "      <td>5963.00</td>\n",
       "      <td>6588.00</td>\n",
       "      <td>7501.00</td>\n",
       "      <td>8808.00</td>\n",
       "      <td>10286.00</td>\n",
       "      <td>11849.0</td>\n",
       "      <td>13802.00</td>\n",
       "      <td>16110.00</td>\n",
       "      <td>19184.00</td>\n",
       "      <td>22686.00</td>\n",
       "      <td>25022.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "time_period  1958-1962  1963-1967  1968-1972  1973-1977  1978-1982  1983-1987  \\\n",
       "country                                                                         \n",
       "Afghanistan    9344.00   10369.00   11717.00   13056.00   12667.00   11338.00   \n",
       "Albania        1738.00    1999.00    2254.00    2518.00    2788.00    3121.00   \n",
       "Algeria       11690.00   13354.00   15377.00   17690.00   20576.00   23918.00   \n",
       "Andorra          15.38      20.75      26.89      32.77      39.11      48.46   \n",
       "Angola         5466.00    5963.00    6588.00    7501.00    8808.00   10286.00   \n",
       "\n",
       "time_period  1988-1992  1993-1997  1998-2002  2003-2007  2008-2012  2013-2017  \n",
       "country                                                                        \n",
       "Afghanistan    13746.0   18034.00   21487.00   25878.00   29727.00   32527.00  \n",
       "Albania         3241.0    3092.00    3123.00    3011.00    2881.00    2897.00  \n",
       "Algeria        27181.0   29888.00   31990.00   34262.00   37439.00   39667.00  \n",
       "Andorra           58.9      64.15      71.05      84.88      79.32      70.47  \n",
       "Angola         11849.0   13802.00   16110.00   19184.00   22686.00   25022.00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "variable_slice(data, 'total_pop').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time series for given country and variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.763721Z",
     "start_time": "2017-06-04T02:08:50.752561Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def time_series(df, country, variable):\n",
    "    \n",
    "    # Only take data for country/variable combo \n",
    "    series = df[(df.country==country) & (df.variable==variable)]\n",
    "    \n",
    "    # Drop years with no data \n",
    "    series = series.dropna()[['year_measured', 'value']]\n",
    "    \n",
    "    # Change years to int and set as index \n",
    "    series.year_measured = series.year_measured.astype(int)\n",
    "    series.set_index('year_measured', inplace=True)\n",
    "    series.columns = [variable]\n",
    "    return series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.837187Z",
     "start_time": "2017-06-04T02:08:50.765197Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_pop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_measured</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1992</th>\n",
       "      <td>10235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>10091.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>9826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>9556.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>9491.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>9496.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               total_pop\n",
       "year_measured           \n",
       "1992             10235.0\n",
       "1997             10091.0\n",
       "2002              9826.0\n",
       "2007              9556.0\n",
       "2012              9491.0\n",
       "2015              9496.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_series(data, 'Belarus', 'total_pop')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## By region"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们可能需要查看某些评估数据的子集。区域是一种直观的数据细分方式。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.847401Z",
     "start_time": "2017-06-04T02:08:50.838520Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['World | Asia',\n",
       "       'Americas | Central America and Caribbean | Central America',\n",
       "       'Americas | Central America and Caribbean | Greater Antilles',\n",
       "       'Americas | Central America and Caribbean | Lesser Antilles and Bahamas',\n",
       "       'Americas | Northern America | Northern America',\n",
       "       'Americas | Northern America | Mexico',\n",
       "       'Americas | Southern America | Guyana',\n",
       "       'Americas | Southern America | Andean',\n",
       "       'Americas | Southern America | Brazil',\n",
       "       'Americas | Southern America | Southern America', 'World | Africa',\n",
       "       'World | Europe', 'World | Oceania'], dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.region.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "减少区域数量有助于模式评估。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "创建一个字典来查找新的、更简单的区域（亚洲、北美洲、南美洲、非洲、欧洲、大洋洲）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.853399Z",
     "start_time": "2017-06-04T02:08:50.848644Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "simple_regions ={\n",
    "    'World | Asia':'Asia',\n",
    "    'Americas | Central America and Caribbean | Central America': 'North America',\n",
    "    'Americas | Central America and Caribbean | Greater Antilles': 'North America',\n",
    "    'Americas | Central America and Caribbean | Lesser Antilles and Bahamas': 'North America',\n",
    "    'Americas | Northern America | Northern America': 'North America',\n",
    "    'Americas | Northern America | Mexico': 'North America',\n",
    "    'Americas | Southern America | Guyana':'South America',\n",
    "    'Americas | Southern America | Andean':'South America',\n",
    "    'Americas | Southern America | Brazil':'South America',\n",
    "    'Americas | Southern America | Southern America':'South America', \n",
    "    'World | Africa':'Africa',\n",
    "    'World | Europe':'Europe', \n",
    "    'World | Oceania':'Oceania'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.893856Z",
     "start_time": "2017-06-04T02:08:50.854868Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "data.region = data.region.apply(lambda x: simple_regions[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.905717Z",
     "start_time": "2017-06-04T02:08:50.895821Z"
    },
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Asia' 'North America' 'South America' 'Africa' 'Europe' 'Oceania']\n"
     ]
    }
   ],
   "source": [
    "print(data.region.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "提取单个区域的函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-06-04T02:08:50.911309Z",
     "start_time": "2017-06-04T02:08:50.907969Z"
    },
    "collapsed": true,
    "run_control": {
     "frozen": false,
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def subregion(data, region):\n",
    "    return data[data.region==region]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises\n",
    "* Create a dataframe containing each variable for every country for the time period of 1963-1967.\n",
    "* Create a dataframe containing the total renewable surface water for each country over each time period. \n",
    "* Create a dataframe containing the total population of each country in Asia over each time period. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "nav_menu": {},
  "toc": {
   "navigate_menu": true,
   "number_sections": true,
   "sideBar": true,
   "threshold": 6,
   "toc_cell": true,
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
